<?php
$SQL=array(
	"users"=>array(
		"insert"=>"INSERT INTO `voca_users` (name,password,email,created_at) VALUES(?,?,?,now())",
		"get_by_email_and_password"=>"SELECT * FROM `voca_users` WHERE email = ? and password = ?",
		"get_by_email"=>"SELECT * FROM `voca_users` WHERE email = ?",
		"get_by_id"=>"SELECT * FROM `voca_users` WHERE id=?"
	),
	"user_configs"=>array(
		"insert"=>"INSERT INTO `voca_user_configs` (user_id,amount_per_day,`money`) VALUES(?,?,?)",
		"get_by_user_id"=>"SELECT * FROM `voca_user_configs` WHERE user_id = ?"
	),

	"words"=>array(
		"insert"=>"INSERT INTO `voca_words` (word) VALUES(?)",
		"get_id"=>"SELECT id FROM `voca_words` WHERE word = ?",
		"get_by_id"=>"SELECT * FROM `voca_words` WHERE id=?"
	),
	"user_words"=>array(
		"insert"=>"INSERT INTO `voca_user_words` (user_id,word_id,familiarity,created_at) VALUES(?,?,1,now())",
		"update"=>"UPDATE `voca_user_words` SET familiarity=? WHERE user_id = ? AND word_id = ?",
		"delete"=>"DELETE FROM `voca_user_words` WHERE user_id = ? AND word_id = ?",
		"incr_familiarity"=>"UPDATE `voca_user_words` SET familiarity=familiarity+1 WHERE user_id = ? AND word_id=?"
	),
	"word_definitions"=>array(
		"insert"=>"INSERT INTO `voca_definitions` (word_id,user_id,definition,is_standard) VALUES(?,?,?,1)",
		"update"=>"UPDATE `voca_definitions` SET definition=? WHERE word_id = ? AND user_id = ?"
	),

	"sentences"=>array(
		"insert"=>"INSERT INTO `voca_sentences` (word_id,sentence,translation) VALUES (?,?,?)",
		"delete"=>"DELETE FROM `voca_sentences` WHERE id=?",
		"update"=>"UPDATE `voca_sentences` SET sentence=?, translation=? WHERE id=?"
	),
	"user_sentences"=>array(
		"insert"=>"INSERT INTO `voca_user_sentences` (user_id,sentence_id,is_creator) VALUES (?,?,?)",
		"delete"=>"DELETE FROM `voca_user_sentences` WHERE user_id = ? AND sentence_id = ?"
	),
	"user_sentence_blacklist"=>array(
		"insert"=>"INSERT INTO `voca_user_sentence_blacklist` (sentence_id,user_id) VALUES(?,?)",
		"delete_by_sentence_id"=>"DELETE FROM `voca_user_sentence_blacklist` WHERE sentence_id = ? "
	),

	"notes"=>array(
		"insert"=>"INSERT INTO `voca_notes` (word_id,note) VALUES (?,?)",
		"delete"=>"DELETE FROM `voca_notes` WHERE id=?",
		"update"=>"UPDATE `voca_notes` SET note=? WHERE id=?"
	),
	"user_notes"=>array(
		"insert"=>"INSERT INTO `voca_user_notes` (user_id,note_id,is_creator) VALUES (?,?,?)",
		"delete"=>"DELETE FROM `voca_user_notes` WHERE user_id = ? AND note_id = ?"
	),
	"user_note_blacklist"=>array(
		"insert"=>"INSERT INTO `voca_user_note_blacklist` (note_id,user_id) VALUES(?,?)",
		"delete_by_note_id"=>"DELETE FROM `voca_user_note_blacklist` WHERE note_id = ? "
	),
	"groups"=>array(
		"insert"=>"INSERT INTO `voca_groups` (name,user_id,description,created_at) VALUES(?,?,?,now())",
		"update"=>"UPDATE `voca_groups` SET name=?, description=? WHERE id=?",
		"delete"=>"DELETE FROM `voca_groups` WHERE id=?",
		"get_all"=>"SELECT * FROM `voca_groups` ORDER BY prior",
		"get_by_id"=>"SELECT * FROM `voca_groups` WHERE id=?"
	),
	"group_topics"=>array(
		"insert"=>"INSERT INTO `voca_group_topics` (group_id,user_id,title,content,created_at) VALUES(?,?,?,?,now())",
		"update"=>"UPDATE `voca_group_topics` SET title=?, content=?,updated_at=now() WHERE id=? AND user_id=?",
		"get_by_id"=>"SELECT * FROM `voca_group_topics` WHERE id=?",
		"get_count"=>"SELECT COUNT(*) count FROM `voca_group_topics`",
		"delete"=>"DELETE FROM `voca_group_topics` WHERE id=? AND user_id=?"
	),
	"group_replies"=>array(
		"insert"=>"INSERT INTO `voca_group_replies` (topic_id,user_id,content,created_at) VALUES(?,?,?,now())",
		"get_by_topic_id"=>"SELECT voca_group_replies.*, name FROM `voca_group_replies`".
			" LEFT JOIN `voca_users` ON user_id = voca_users.id".
			" WHERE topic_id = ? ORDER BY created_at",
		"get_count"=>"SELECT COUNT(*) count FROM `voca_group_replies` WHERE topic_id=?",
		"update"=>"UPDATE `voca_group_replies` SET content=?,updated_at=now() WHERE id=? AND user_id=?",
		"delete"=>"DELETE FROM `voca_group_replies` WHERE id=? AND user_id=?"
	),
	"daily_records"=>array(
		"insert"=>"INSERT INTO `voca_daily_records` (user_id,num_of_words,created_at,memo) VALUES(?,?,now(),?)",
		"record_of_today" =>" SELECT * FROM `voca_daily_records` WHERE DATE(`created_at`) = CURDATE()"
	)
);
?>